﻿using System;
using System.Runtime.CompilerServices;
using MediaPortal.Configuration;
using MediaPortal.Database;
using MediaPortal.GUI.Library;
using System.IO;
using SQLite.NET;
using System.Collections;
using System.Xml;
using System.Collections.Generic;

namespace MyNetflix2
{
    public class MyNetFlixData
    {
        #region locals

        private bool disposed = false;
        private SQLiteClient m_db = null;
        private string _userId = "";
        private string _accessToken = "";
        private string _accessTokenSecret = "";
        private string _results = "";
        private string _defaultStart = "";
        private string _sharedDatabase = "";
        private string _userName = "";
        private string _instantOnly = "";
        private string _details = "";
        private int _defaultScreen = 0;
        public List<string> userList;
        public ArrayList _netflixUsers;
        XmlDocument xdoc480 = new XmlDocument(); //xml doc used for xml parsing
        XmlDocument xdoc720 = new XmlDocument(); //xml doc used for xml parsing

        #endregion

        #region Properties

        public string UserId
        {
            get
            {
                return _userId;
            }
            set
            {
                _userId = value;
            }
        }

        public string AccessToken
        {
            get
            {
                return _accessToken;
            }
            set
            {
                _accessToken = value;
            }
        }

        public string AccessTokenSecret
        {
            get
            {
                return _accessTokenSecret;
            }
            set
            {
                _accessTokenSecret = value;
            }
        }


        public string Results
        {
            get
            {
                return _results;
            }
            set
            {
                _results = value;
            }
        }

        public string DefaultStart
        {
            get
            {
                return _defaultStart;
            }
            set
            {
                _defaultStart = value;
            }
        }

        public string SharedDatabase
        {
            get
            {
                return _sharedDatabase;
            }
            set
            {
                _sharedDatabase = value;
            }
        }

        public string UserName
        {
            get
            {
                return _userName;
            }
            set
            {
                _userName = value;
            }
        }

        public string Details
        {
            get
            {
                return _details;
            }
            set
            {
                _details = value;
            }
        }

        public string InstantOnly
        {
            get
            {
                return _instantOnly;
            }
            set
            {
                _instantOnly = value;
            }
        }

        public int DefaultScreen
        {
            get
            {
                return _defaultScreen;
            }
            set
            {
                _defaultScreen = value;
            }
        }


        #endregion

        #region Constructor & Destructor
        /// <summary>
        /// Constructor
        /// </summary>
        public MyNetFlixData()
        {
            //--------------------------------------------------------------------------------
            // Open the database
            //--------------------------------------------------------------------------------
            Open();
        }

        /// <summary>
        /// Dispose of the databse object
        /// </summary>
        public void Dispose()
        {
            //--------------------------------------------------------------------------------
            // Lets dispose of the database object
            //--------------------------------------------------------------------------------
            if (!disposed)
            {
                disposed = true;
                if (m_db != null)
                {
                    try
                    {
                        m_db.Close();
                        m_db.Dispose();
                    }
                    catch (Exception)
                    {
                    }
                    m_db = null;
                }
            }
        }

        #endregion

        /// <summary>
        /// Open the database
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        private void Open()
        {
            try
            {
                //--------------------------------------------------------------------------------
                // Maybe called by an exception
                //--------------------------------------------------------------------------------
                if (m_db != null)
                {
                    try
                    {
                        m_db.Close();
                        m_db.Dispose();
                        Log.Warn("My Netflix - MyNetFlixData : Open: Disposing current instance.");
                    }
                    catch (Exception ex)
                    {
                        Log.Error("My Netflix - MyNetFlixData:Open : Error Disposing Database: " + ex.Message);
                    }
                }

                //--------------------------------------------------------------------------------
                // Open database
                //--------------------------------------------------------------------------------
                try
                {
                    Directory.CreateDirectory(Config.GetFolder(Config.Dir.Database));
                }
                catch (Exception ex)
                {
                    Log.Error("My Netflix - MyNetFlixData : Open: Error Opening Database: " + ex.Message);
                }

                m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "NetFlixDatabaseV2.db3"));

                //--------------------------------------------------------------------------------
                // Retry 10 times on busy (DB in use or system resources exhausted)
                //--------------------------------------------------------------------------------
                m_db.BusyRetries = 10;

                //--------------------------------------------------------------------------------
                // Wait 100 ms between each try (default 10)
                //--------------------------------------------------------------------------------
                m_db.BusyRetryDelay = 100;

                DatabaseUtility.SetPragmas(m_db);
                CreateTables();

                GetSettings();
            }
            catch (Exception ex)
            {
                Log.Error("My Netflix - MyNetFlixData:Open: Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
                Open();
            }
        }

        /// <summary>
        /// Create Database Tables
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        private bool CreateTables()
        {
            //--------------------------------------------------------------------------------
            // If no database, do nothing and return.
            //--------------------------------------------------------------------------------
            if (m_db == null)
            {
                return false;
            }

            //--------------------------------------------------------------------------------
            // Add the User Table
            //--------------------------------------------------------------------------------
            DatabaseUtility.AddTable(m_db, "settings", "CREATE TABLE settings ( idUser integer primary key, strResults text, strInstantOnly text, strDetails text, strScreen text)");
            DatabaseUtility.AddTable(m_db, "queueUsers", "CREATE TABLE queueUsers ( idUser integer primary key, strUserId text, strUserName text, strAccessToken text, strAccessTokenSecret text, iDefault integer)");
            DatabaseUtility.AddTable(m_db, "instantUsers", "CREATE TABLE instantUsers (idUser integer primary key, strUserName text)");
            DatabaseUtility.AddTable(m_db, "userTitles", "CREATE TABLE userTitles (idUser integer primary key, iUserId integer, strTitleId text)");
            DatabaseUtility.AddTable(m_db, "titlesWatched", "CREATE TABLE titlesWatched ( watchedid integer primary key, strMovie text)");
            DatabaseUtility.AddTable(m_db, "myview", "CREATE TABLE myview ( viewid integer primary key, iView integer, strQueue text)");
            DatabaseUtility.AddTable(m_db, "trailers", "CREATE TABLE trailers ( trailerid integer primary key, strTitle text, strTrailer text, iRes integer)");
            return true;
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public void GetSettings()
        {
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from settings";
            SQLiteResultSet results = m_db.Execute(strSQL);


            //--------------------------------------------------------------------------------
            // Save the settings to local variables
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                _results = DatabaseUtility.Get(results, 0, "strResults").ToString();
                _defaultStart = DatabaseUtility.Get(results, 0, "strDefaultStart").ToString();
                _instantOnly = DatabaseUtility.Get(results, 0, "strInstantOnly").ToString();
                _details = DatabaseUtility.Get(results, 0, "strDetails").ToString();
                _defaultScreen = int.Parse(DatabaseUtility.Get(results, 0, "strScreen").ToString());
            }
        }

        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int AddSettings(string strResults, string strInstantOnly, string strDetails, int iScreen)
        {
            //--------------------------------------------------------------------------------
            // Delete all settings
            //--------------------------------------------------------------------------------
            string strSQL = "select * from settings";
            SQLiteResultSet results = m_db.Execute(strSQL);
            if (results.Rows.Count > 0)
            {
                strSQL = "begin";
                results = m_db.Execute(strSQL);

                strSQL = "delete from settings";
                results = m_db.Execute(strSQL);

                strSQL = "commit";
                results = m_db.Execute(strSQL);
            }


            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            strSQL = "begin";
            results = m_db.Execute(strSQL);

            strSQL = String.Format("insert into settings (idUser, strResults, strInstantOnly, strDetails, strScreen) values(null, '{0}', '{1}', '{2}', '{3}')", strResults, strInstantOnly, strDetails, iScreen.ToString());
            results = m_db.Execute(strSQL);

            if (results.Rows.Count > 0)
            {
                Log.Info("My NetFlix - MyNetFlixData:AddSettings: Settings Saved");
            }
            strSQL = "commit";
            results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public string GetDefaultUser()
        {
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from queueUsers where iDefault=1";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                return DatabaseUtility.Get(results, 0, "strUserName").ToString(); ;
            }
            else
                return "";
        }

        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int AddQueueUser()
        {
            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            string strSQL = "begin";
            SQLiteResultSet results = m_db.Execute(strSQL);

            strSQL = String.Format("insert into queueUsers (idUser, strUserId, strUserName, strAccessToken,strAccessTokenSecret) values(null, '{0}','{1}','{2}','{3}')", _userId, _userName, _accessToken, _accessTokenSecret);
            results = m_db.Execute(strSQL);

            if (results.Rows.Count > 0)
            {
                Log.Info("MyNetFlixData : AddUser: User {0} has been added to database", _userId);
            }
            strSQL = "commit";
            results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Get User Information for Authentication
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public bool GetQueueUser(string userName)
        {
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from queueUsers where strUserName='" + userName + "'";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                _userName = DatabaseUtility.Get(results, 0, "strUserName").ToString();
                _userId = DatabaseUtility.Get(results, 0, "strUserId").ToString();
                _accessToken = DatabaseUtility.Get(results, 0, "strAccessToken").ToString();
                _accessTokenSecret = DatabaseUtility.Get(results, 0, "strAccessTokenSecret").ToString();
                return true;
            }
            else
                return false;
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public List<string> GetQueueUsers()
        {
            userList = new List<string>();
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from queueUsers";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                int i = 0;
                foreach (SQLiteResultSet.Row row in results.Rows)
                {
                    userList.Add(DatabaseUtility.Get(results, i, "strUserName").ToString());
                    i++;
                }
                return userList;
            }
            else
                return userList;
        }

        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int AddInstantUsers(string userName)
        {
            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            string strSQL = "begin";
            SQLiteResultSet results = m_db.Execute(strSQL);

            strSQL = String.Format("insert into instantUsers (idUser, strUserName) values(null, '{0}')", userName);
            results = m_db.Execute(strSQL);

            if (results.Rows.Count > 0)
            {
                Log.Info("MyNetFlixData:AddInstantUsers: User {0} has been added to database");
            }
            strSQL = "commit";
            results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public List<string> GetInstantUsers()
        {
            userList = new List<string>();
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from instantUsers";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                int i = 0;
                foreach (SQLiteResultSet.Row row in results.Rows)
                {
                    _userName = DatabaseUtility.Get(results, i, "strUserName").ToString();
                    userList.Add(_userName);
                    i++;
                }
                return userList;
            }
            else
                return userList;
        }

        /// <summary>
        /// Get User Information for Authentication
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int GetInstantUser(string userName)
        {
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from instantUsers where strUserName='" + userName + "'";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {

                return int.Parse(DatabaseUtility.Get(results, 0, "idUser").ToString());
            }
            else
                return -1;
        }


        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int UpdateDefault(string userName)
        {
            string strSQL = "begin";
            SQLiteResultSet results = m_db.Execute(strSQL);

            strSQL = String.Format("update queueUsers set iDefault = 0");
            results = m_db.Execute(strSQL);

            strSQL = "commit";
            results = m_db.Execute(strSQL);
            results = null;


            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            strSQL = "begin";
            results = m_db.Execute(strSQL);

            strSQL = String.Format("update queueUsers set iDefault=1 where strUserName='{0}'", userName);
            results = m_db.Execute(strSQL);

            if (results.Rows.Count > 0)
            {
                Log.Info("MyNetFlixData:UpdateDefault: User {0} has been updated");
            }
            strSQL = "commit";
            results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public List<string> GetTitles(int iUserId)
        {
            List<string> movieList = new List<string>();
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from userTitles where iUserId=" + iUserId;
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                foreach (SQLiteResultSet.Row row in results.Rows)
                {
                    movieList.Add(row.fields[2].ToString());
                }
                return movieList;
            }
            else
                return movieList;
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public List<string> GetAllTitles()
        {
            List<string> movieList = new List<string>();
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from userTitles";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                foreach (SQLiteResultSet.Row row in results.Rows)
                {
                    movieList.Add(row.fields[2].ToString());
                }
                return movieList;
            }
            else
                return movieList;
        }

        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int AddTitle(int iUserId, string movieId)
        {
            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            string strSQL = "begin";
            SQLiteResultSet results = m_db.Execute(strSQL);

            strSQL = String.Format("insert into userTitles (idUser, iUserId, strTitleId) values(null, {0},'{1}')", iUserId, movieId);
            results = m_db.Execute(strSQL);

            if (results.Rows.Count > 0)
            {
                Log.Debug("My NetFlix - MyNetFlixData:AddUser: Movie {0} has been added to database", movieId);
            }
            strSQL = "commit";
            results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int UpdateTitle(int iUserId, string movieId)
        {
            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            string strSQL = "begin";
            SQLiteResultSet results = m_db.Execute(strSQL);

            strSQL = String.Format("update userTitles set iUserId={0} where strTitleId='{1}'", iUserId, movieId);
            results = m_db.Execute(strSQL);

            if (results.Rows.Count > 0)
            {
                Log.Debug("My NetFlix - MyNetFlixData:MoveTitle: Movie {0} has been updated in the database", movieId);
            }
            strSQL = "commit";
            results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Add Watched Movie
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int AddMovieWatched(string sMovie)
        {
            //--------------------------------------------------------------------------------
            // Build the sql string and execute
            //--------------------------------------------------------------------------------
            string strSQL = String.Format("select * from titlesWatched where strMovie = '" + sMovie + "'");
            SQLiteResultSet results = m_db.Execute(strSQL);

            if (results.Rows.Count < 1)
            {
                strSQL = "begin";
                results = m_db.Execute(strSQL);

                strSQL = String.Format("insert into titlesWatched (watchedid, strMovie) values(null, '{0}')", sMovie);
                results = m_db.Execute(strSQL);

                if (results.Rows.Count > 0)
                {
                    Log.Info("MyNetFlixData:AddWatchedMovie: Movie {0} has been added to database", sMovie);
                }
                strSQL = "commit";
                results = m_db.Execute(strSQL);

                return m_db.LastInsertID();
            }
            else
                return -1;
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public List<string> GetWatchedMovies()
        {
            List<string> sMovies = new List<string>();
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select strMovie from titlesWatched";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                foreach (SQLiteResultSet.Row row in results.Rows)
                {
                    sMovies.Add(row.fields[0].ToString());
                }
            }
            return sMovies;
        }

        /// <summary>
        /// Add User Information
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int AddMyView(int iView, MyNetFlixMain.FacadeView view)
        {
            //--------------------------------------------------------------------------------
            // Delete all Browser Information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from myview where strQueue = '" + view.ToString() + "'";
            SQLiteResultSet results = m_db.Execute(strSQL);
            if (results.Rows.Count > 0)
            {
                strSQL = "begin";
                results = m_db.Execute(strSQL);

                strSQL = String.Format("update myview set iview={0},strQueue='{1}' where strQueue='{1}'", iView, view.ToString());
                results = m_db.Execute(strSQL);

                strSQL = "commit";
                results = m_db.Execute(strSQL);
            }
            else
            {
                //--------------------------------------------------------------------------------
                // Build the sql string and execute
                //--------------------------------------------------------------------------------
                strSQL = "begin";
                results = m_db.Execute(strSQL);

                strSQL = String.Format("insert into myview (viewid, iView, strQueue) values(null, {0},'{1}')", iView, view.ToString());
                results = m_db.Execute(strSQL);

                if (results.Rows.Count > 0)
                {
                    Log.Info("MyNetFlixData:AddMyView; View {0} has been added to database", iView);
                }

                strSQL = "commit";
                results = m_db.Execute(strSQL);
            }

            //--------------------------------------------------------------------------------
            // Return the userid
            //--------------------------------------------------------------------------------
            return m_db.LastInsertID();
        }

        /// <summary>
        /// Get User Information for Authorize
        /// </summary>
        [MethodImpl(MethodImplOptions.Synchronized)]
        public int GetMyView(MyNetFlixMain.FacadeView view)
        {
            //--------------------------------------------------------------------------------
            // Get all user information
            //--------------------------------------------------------------------------------
            string strSQL = "select * from myview where strQueue='" + view + "'";
            SQLiteResultSet results = m_db.Execute(strSQL);

            //--------------------------------------------------------------------------------
            // If there is a user, populate the properties, else return false
            //--------------------------------------------------------------------------------
            if (results != null && results.Rows.Count > 0)
            {
                return int.Parse(DatabaseUtility.Get(results, 0, "iView").ToString());
            }
            else
                return -1;
        }

    }
}
